package com.zr.daoimp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.LoginDaoYR;
import com.zr.model.User;
import com.zr.util.JDBCUtil;

/**
 * Login表的Dao实现层
 * @author YR
 *
 */
public class LoginDaoImplYR implements LoginDaoYR{

	@Override
	public List<User> selcusByPage(int page, int rows) {
		//新建一个User集合
		List<User> lu=new ArrayList<User>();
		//sql语句,查询出某页的User
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT * FROM dcxt_login");
		sql.append(" where urole=3");
		sql.append(" LIMIT ?,?");
		//通过工具包建立connection
		Connection  con = JDBCUtil.getConnection();
		//声明预编译器
		PreparedStatement pst=null;
		try {
			pst=con.prepareStatement(sql.toString());
		    pst.setInt(1, (page-1)*rows);
		    pst.setInt(2, rows);
		    ResultSet rs=pst.executeQuery();
		  //将查询出的每个User封装到一个User对象中并放入List
		    while(rs.next()){
		    	User user=new User();
		    	user.setUid(""+rs.getInt("uid"));
		    	user.setUname(rs.getString("uname"));
		    	user.setUpasw(rs.getString("upassword"));
		    	user.setUphone(""+rs.getInt("uphone"));
		    	user.setUrole(""+rs.getInt("urole"));
		    	lu.add(user);
		    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lu;
	}

	@Override
	public int selallcus() {
		int total=0;
		// SQL语句，查询顾客总数
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT count(uid) FROM dcxt_login");
		sql.append(" where urole=3");
		//通过工具包建立connection
		Connection  con = JDBCUtil.getConnection();
		//声明预编译器
		PreparedStatement pst=null;
		try {
			pst=con.prepareStatement(sql.toString());
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				total=rs.getInt("count(uid)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public List<User> selbossByPage(int page, int rows) {
		//新建一个User集合
		List<User> lb = new ArrayList<User>();
		// sql语句,查询出某页的User
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM dcxt_login");
		sql.append(" where urole=2");
		sql.append(" LIMIT ?,?");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page - 1) * rows);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			// 将查询出的每个User封装到一个User对象中并放入List
			while (rs.next()) {
				User user = new User();
				user.setUid("" + rs.getInt("uid"));
				user.setUname(rs.getString("uname"));
				user.setUpasw(rs.getString("upassword"));
				user.setUphone("" + rs.getInt("uphone"));
				user.setUrole("" + rs.getInt("urole"));
				lb.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lb;
	}

	@Override
	public int selallboss() {
		int total=0;
		// SQL语句，查询顾客总数
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT count(uid) FROM dcxt_login");
		sql.append(" where urole=2");
		//通过工具包建立connection
		Connection  con = JDBCUtil.getConnection();
		//声明预编译器
		PreparedStatement pst=null;
		try {
			pst=con.prepareStatement(sql.toString());
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				total=rs.getInt("count(uid)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public User selclouserByUid(int uid) {
		//新建一个User对象
		User user = new User();
		// sql语句,查询出某id的User
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM dcxt_login");
		sql.append(" where uid=?");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,uid);
			ResultSet rs = pst.executeQuery();
			// 将查询出的User封装到一个User对象中
			if(rs.next()) {
				user.setUid("" + rs.getInt("uid"));
				user.setUname(rs.getString("uname"));
				user.setUpasw(rs.getString("upassword"));
				user.setUphone("" + rs.getInt("uphone"));
				user.setUrole("" + rs.getInt("urole"));
					}
		} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
		}
		try {
					JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
		}
		return user;
	}

	@Override
	public void clouserByUid(int uid) {
		// sql语句,将某用户数据库中的urole值设置为0，以此来封禁该用户
		StringBuffer sql = new StringBuffer();
		sql.append("update dcxt_login");
		sql.append(" set urole=0");
		sql.append(" where uid=?");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,uid);
		    pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public int getUidByUname(String uname) {
		int uid=0;
		// sql语句,通过uname获取uid
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT uid FROM dcxt_login");
		sql.append(" where uname=?");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,uname);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				uid=rs.getInt("uid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return uid;
	}

	@Override
	public void openuserByUidUrole(int uid, int urole) {
		// sql语句,将某用户数据库中的urole值设置为被封之前状态，以此来解封该用户
		StringBuffer sql = new StringBuffer();
		sql.append("update dcxt_login");
		sql.append(" set urole=?");
		sql.append(" where uid=?");
		// 通过工具包建立connection
		Connection con = JDBCUtil.getConnection();
		// 声明预编译器
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, urole);
			pst.setInt(2, uid);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
